DROP TABLE IF EXISTS jms_dm.dm_waybill_prescription_reach_details_dt ;
CREATE EXTERNAL TABLE jms_dm.dm_waybill_prescription_reach_details_dt(
  taking_date date COMMENT ' 揽收日期',
  island bigint COMMENT '订单来源 1oppo电商 2VIP系统 3桃花岛 4抖音 5快手 6其它',
  waybill_no string COMMENT ' 运单号',
  customer_code string COMMENT ' 客户编号',
  customer_name string COMMENT ' 客户名称',
  start_subordinate_agent_code string COMMENT ' 始发代理区code',
  start_subordinate_agent_name string COMMENT ' 始发代理区name',
  start_parent_network_code string COMMENT ' 始发加盟商code',
  start_parent_network_name string COMMENT ' 始发加盟商name',
  start_regional_id string COMMENT ' 始发大区id',
  start_regional_desc string COMMENT ' 始发大区name',
  start_provider_id string COMMENT ' 始发省份id',
  start_provider_desc string COMMENT ' 始发省份name',
  start_city_id string COMMENT ' 始发城市id',
  start_city_desc string COMMENT ' 始发城市name',
  start_area_id string COMMENT ' 始发区id',
  start_area_desc string COMMENT ' 始发区name',
  start_pick_network_code string COMMENT ' 始发网点code',
  start_pick_network_name string COMMENT ' 始发网点name',
  aim_subordinate_agent_code string COMMENT ' 目的代理区code',
  aim_subordinate_agent_name string COMMENT ' 目的代理区name',
  aim_parent_network_code string COMMENT ' 目的加盟商code',
  aim_parent_network_name string COMMENT ' 目的加盟商name',
  aim_regional_id string COMMENT ' 目的大区id',
  aim_regional_desc string COMMENT ' 目的大区name',
  aim_provider_id string COMMENT ' 目的省份id',
  aim_provider_desc string COMMENT ' 目的省份name',
  aim_city_id string COMMENT ' 目的城市id',
  aim_city_desc string COMMENT ' 目的城市name',
  aim_area_id string COMMENT ' 目的区id',
  aim_area_desc string COMMENT ' 目的区name',
  aim_destination_code string COMMENT ' 目的网点code',
  aim_destination_name string COMMENT ' 目的网点name',
  taking_time timestamp COMMENT ' 揽收时间',
  warehous_time timestamp COMMENT ' 入仓时间',
  send_time timestamp COMMENT ' 发件扫描时间',
  start_center_code string COMMENT ' 始发转运中心code',
  start_center_name string COMMENT ' 始发转运中心name',
  start_centerarriva_time timestamp COMMENT ' 始发中心到件扫描时间',
  arrival_task_no string COMMENT ' 始发中心到车任务号',
  type_one string COMMENT ' 类型1',
  start_centersend_time timestamp COMMENT ' 始发中心发件扫描时间',
  departure_task string COMMENT ' 始发发车任务单号',
  start_centersend_departure_time timestamp COMMENT '始发中心发车时间',
  aim_center_code string COMMENT ' 目的转运中心code',
  aim_center_name string COMMENT ' 目的转运中心name',
  aim_centerarriva_time timestamp COMMENT ' 目的中心到件扫描时间',
  arrival_task string COMMENT ' 目的中心到车任务单号',
  aim_centerarrival_departure_time timestamp COMMENT ' 目的中心到车时间',
  type_two string COMMENT ' 类型2',
  aim_centersend_time timestamp COMMENT ' 目的中心发件扫描时间',
  sign_network_code string COMMENT ' 签收网点code',
  sign_network_name string COMMENT ' 签收网点name',
  sign_subordinate_agent_code string COMMENT ' 签收代理区code',
  sign_subordinate_agent_name string COMMENT ' 签收代理区name',
  sign_parent_network_code string COMMENT ' 签收加盟商code',
  sign_parent_network_name string COMMENT ' 签收加盟商name',
  sign_regional_id string COMMENT ' 签收大区id',
  sign_regional_desc string COMMENT ' 签收大区name',
  sign_provider_id string COMMENT ' 签收省份id',
  sign_provider_desc string COMMENT ' 签收省份name',
  sign_city_id string COMMENT ' 签收城市id',
  sign_city_desc string COMMENT ' 签收城市name',
  sign_area_id string COMMENT ' 签收区id',
  sign_area_desc string COMMENT ' 签收区name',
  pres_sign_time timestamp COMMENT ' 时效签收时间',
  real_sign_time timestamp COMMENT ' 实际签收时间',
  deliverytotaltime bigint COMMENT '派件总耗时',
  plan_sign_date timestamp COMMENT '规划应签收时间',
  dispatch_sign_time timestamp COMMENT '派件应签收时间',
  is_delivery_delay bigint COMMENT '交件是否延误 0否1是',
  is_transport_delay bigint COMMENT '转运是否延误 0否1是',
  is_sign_delay bigint COMMENT '签收是否延误 0否1是',
  delay_type string COMMENT '延误类型',
  districtprescription bigint COMMENT '区县时效',
  prescription bigint COMMENT '实际运行时效',
  is_real_standard bigint COMMENT '实际是否达标  1是0否',
  pres_prescription bigint COMMENT '时效运行时效',
  is_pres_standard bigint COMMENT '时效是否达标  1是0否',
  is_pres_sign bigint COMMENT '时效签收',
  is_real_sign bigint COMMENT '实际签收',
  economic_circle_type bigint COMMENT '经济圈名称 经济圈名称 1珠三角 2长三角 3京津冀',
  circle_type bigint COMMENT '类型是否经济圈 1是0否',
  platform_effect bigint COMMENT '平台时效',
  is_platform_real_standard bigint COMMENT '平台实际是否达标  1是0否',
  is_platform_pres_standard bigint COMMENT '平台时效是否达标  1是0否',
  platform_effect_type bigint COMMENT '是否平台时效 1是0否')
COMMENT '区县时效3期达成率明细表'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyy-MM-dd)')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_waybill_prescription_reach_details_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );

MSCK REPAIR TABLE jms_dm.dm_waybill_prescription_reach_details_dt;







DROP TABLE IF EXISTS jms_dm.dm_agencyarea_prescription_reach_rate_dt ;
CREATE EXTERNAL TABLE jms_dm.dm_agencyarea_prescription_reach_rate_dt(
  date_time date COMMENT '日期',
  island bigint COMMENT '订单来源',
  districtprescription bigint COMMENT '区县时效',
  is_pres_sign bigint COMMENT '时效签收',
  is_real_sign bigint COMMENT '实际签收',
  start_subordinate_agent_code bigint COMMENT '始发代理区code',
  sign_subordinate_agent_code bigint COMMENT '签收代理区code',
  start_subordinate_agent_name string COMMENT '始发代理区name',
  start_regional_id string COMMENT '始发大区id',
  start_regional_desc string COMMENT '始发大区name',
  start_provider_id string COMMENT '始发省份id',
  start_provider_desc string COMMENT '始发省份name',
  sign_subordinate_agent_name string COMMENT '签收代理区name',
  sign_regional_id string COMMENT '签收大区id',
  sign_regional_desc string COMMENT '签收大区name',
  sign_provider_id string COMMENT '签收省份id',
  sign_provider_desc string COMMENT '签收省份name',
  economic_circle_type bigint COMMENT '经济圈名称',
  circle_type bigint COMMENT '是否经济圈',
  platform_effect_type bigint COMMENT '是否平台时效',
  platform_effect bigint COMMENT '平台时效',
  real_reach_sum bigint COMMENT '实际达成票数',
  real_no_reach_sum bigint COMMENT '实际未达成票数',
  real_total bigint COMMENT '实际总票量',
  real_reach_rate bigint COMMENT '实际达成率',
  pres_reach_sum bigint COMMENT '时效达成票数',
  pres_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_total bigint COMMENT '时效总票量',
  pres_reach_rate bigint COMMENT '时效达成率',
  real_platform_reach_sum bigint COMMENT '实际达成票数',
  real_platform_no_reach_sum bigint COMMENT '实际未达成票数',
  real_platform_total bigint COMMENT '实际总票量',
  real_platform_reach_rate bigint COMMENT '实际达成率',
  pres_platform_reach_sum bigint COMMENT '时效达成票数',
  pres_platform_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_platform_total bigint COMMENT '时效总票量',
  pres_platform_reach_rate bigint COMMENT '时效达成率')
COMMENT '区县时效3期代理区-代理区汇总表'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyyMMdd)')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_agencyarea_prescription_reach_rate_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );

MSCK REPAIR TABLE jms_dm.dm_agencyarea_prescription_reach_rate_dt;




DROP TABLE IF EXISTS jms_dm.dm_agencyarea_prescription_reach_rate_plan_dt ;
CREATE EXTERNAL TABLE jms_dm.dm_agencyarea_prescription_reach_rate_plan_dt(
  date_time date COMMENT '日期',
  island bigint COMMENT '订单来源',
  districtprescription bigint COMMENT '区县时效',
  is_pres_sign bigint COMMENT '时效签收',
  is_real_sign bigint COMMENT '实际签收',
  start_subordinate_agent_code bigint COMMENT '始发代理区code',
  sign_subordinate_agent_code bigint COMMENT '签收代理区code',
  start_subordinate_agent_name string COMMENT '始发代理区name',
  start_regional_id string COMMENT '始发大区id',
  start_regional_desc string COMMENT '始发大区name',
  start_provider_id string COMMENT '始发省份id',
  start_provider_desc string COMMENT '始发省份name',
  sign_subordinate_agent_name string COMMENT '签收代理区name',
  sign_regional_id string COMMENT '签收大区id',
  sign_regional_desc string COMMENT '签收大区name',
  sign_provider_id string COMMENT '签收省份id',
  sign_provider_desc string COMMENT '签收省份name',
  economic_circle_type bigint COMMENT '经济圈名称',
  circle_type bigint COMMENT '是否经济圈',
  platform_effect_type bigint COMMENT '是否平台时效',
  platform_effect bigint COMMENT '平台时效',
  real_reach_sum bigint COMMENT '实际达成票数',
  real_no_reach_sum bigint COMMENT '实际未达成票数',
  real_total bigint COMMENT '实际总票量',
  real_reach_rate bigint COMMENT '实际达成率',
  pres_reach_sum bigint COMMENT '时效达成票数',
  pres_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_total bigint COMMENT '时效总票量',
  pres_reach_rate bigint COMMENT '时效达成率',
  real_platform_reach_sum bigint COMMENT '实际达成票数',
  real_platform_no_reach_sum bigint COMMENT '实际未达成票数',
  real_platform_total bigint COMMENT '实际总票量',
  real_platform_reach_rate bigint COMMENT '实际达成率',
  pres_platform_reach_sum bigint COMMENT '时效达成票数',
  pres_platform_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_platform_total bigint COMMENT '时效总票量',
  pres_platform_reach_rate bigint COMMENT '时效达成率')
COMMENT '区县时效3期代理区-代理区汇总表'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyyMMdd)')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_agencyarea_prescription_reach_rate_plan_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );

MSCK REPAIR TABLE jms_dm.dm_agencyarea_prescription_reach_rate_plan_dt;
